﻿Imports HMSLibrary
Imports System.Data.Common
Imports System.Collections.ObjectModel

Public Class MasterDataLayer
    Inherits MasterDataProvider

    Dim SQLQuery As String = String.Empty

    Public Overrides Function GetCompanyInfo() As HMSLibrary.CompanyInfo
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Select Top 1 * From CompanyMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)

            Dim CompanyObject As New CompanyInfo
            If dr.Read = True Then
                CompanyObject.Name = dr.Item(1)
                CompanyObject.Address = DataCommon.DBNullCheck(dr.Item(2))
                CompanyObject.Description = DataCommon.DBNullCheck(dr.Item(4))
                CompanyObject.TelNo = DataCommon.DBNullCheck(dr.Item(3))
                CompanyObject.VATNo = DataCommon.DBNullCheck(dr.Item(5))
                CompanyObject.CSTNo = DataCommon.DBNullCheck(dr.Item(6))
                CompanyObject.InvoiceTerm = DataCommon.DBNullCheck(dr.Item(7))
                If IsDBNull(dr.Item(8)) = False Then
                    CompanyObject.ItemNoAuto = CType(dr.Item(8), Boolean)
                End If
                CompanyObject.SalesBillPrefix = DataCommon.DBNullCheck(dr.Item(9))
                CompanyObject.SalesCurrentNo = Val(DataCommon.DBNullCheck(dr.Item(10)))
                CompanyObject.SalesSufix = DataCommon.DBNullCheck(dr.Item(11))
            End If
            dr.Close()
            Return CompanyObject
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetItemByID(ByVal ID As Integer) As HMSLibrary.ItemsService

    End Function

    Public Overrides Function GetItemList() As System.Collections.Generic.List(Of HMSLibrary.ItemsService)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Proc_GetInventoryItemMaster"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            Dim itemsList As New List(Of ItemsService)
            Dim itemsObject As ItemsService
            While dr.Read
                itemsObject = New ItemsService
                itemsObject.Name = dr.Item("Name")
                itemsObject.ItemCode = dr.Item("ItemNo")
                itemsObject.ID = dr.Item("ItemID")
                itemsObject.Rate = DataCommon.DBNullCheck(dr.Item("Rate"))
                itemsObject.CategoryName = DataCommon.DBNullCheck(dr.Item("Category"))
                itemsObject.UOMName = DataCommon.DBNullCheck(dr.Item("UnitName"))
                itemsObject.UOM = DataCommon.DBNullCheck(dr.Item("Unit"))
                itemsObject.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                itemsObject.SubCategoryName = DataCommon.DBNullCheck(dr.Item("SubCategory"))
                itemsObject.IsInventoryItem = DataCommon.DBNullCheck(dr.Item("IsInventoryItem"))
                itemsList.Add(itemsObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return itemsList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetLookupByID(ByVal ID As Integer) As HMSLibrary.Lookups

    End Function

    Public Overrides Function GetLookupList(ByVal LookupType As Integer) As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select [Name],ID,Rate From LookupsMaster where Type = '" & LookupType & "' Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("ID")
                lup.Name = dr.Item("Name")
                lup.Rate = DataCommon.DBNullCheck(dr.Item("Rate"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetVendorByID(ByVal ID As Integer) As HMSLibrary.Vendor

    End Function

    Public Overrides Function GetVendorList() As System.Collections.Generic.List(Of HMSLibrary.Vendor)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Select SupplierID, Name, Address, [Telephone No], [Mobile No], [Contact Person], Email, CreditLimit, Address1 From SupplierMaster order by 2"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            Dim vendorList As New List(Of Vendor)
            Dim vendorObject As Vendor
            While dr.Read
                vendorObject = New Vendor
                vendorObject.ID = dr.Item("SupplierID")
                vendorObject.Name = dr.Item("Name")
                vendorObject.Address = DataCommon.DBNullCheck(dr.Item("Address"))
                vendorObject.TelNo = DataCommon.DBNullCheck(dr.Item("Telephone No"))
                vendorObject.MobileNo = DataCommon.DBNullCheck(dr.Item("Mobile No"))
                vendorObject.ContactPerson = DataCommon.DBNullCheck(dr.Item("Contact Person"))
                vendorObject.Email = DataCommon.DBNullCheck(dr.Item("Email"))
                vendorObject.Description = DataCommon.DBNullCheck(dr.Item("Address1"))
                vendorObject.Creditlimit = DataCommon.DBNullCheck(dr.Item("CreditLimit"))
                vendorList.Add(vendorObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return vendorList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function SaveCompanyInfo(ByVal CompanyInfoObject As HMSLibrary.CompanyInfo) As Boolean
        Dim objPRM(10) As Object
        objPRM(0) = CompanyInfoObject.Name
        objPRM(1) = CompanyInfoObject.Address
        objPRM(2) = CompanyInfoObject.TelNo
        objPRM(3) = CompanyInfoObject.Description
        objPRM(4) = CompanyInfoObject.VATNo
        objPRM(5) = CompanyInfoObject.CSTNo
        objPRM(6) = CompanyInfoObject.InvoiceTerm
        objPRM(7) = CompanyInfoObject.ItemNoAuto
        objPRM(8) = CompanyInfoObject.SalesBillPrefix
        objPRM(9) = CompanyInfoObject.SalesCurrentNo
        objPRM(10) = CompanyInfoObject.SalesSufix
        If DataCommon.db.ExecuteNonQuery("Proc_Update_CompanyMaster", objPRM) = 0 Then Return False
        MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        Return True
    End Function

    Public Overloads Overrides Function SaveItem(ByVal ItemObject As HMSLibrary.ItemsService, ByVal Mode As FormAction) As Integer
        Dim objPRM(8) As Object
        If ItemObject.isItemNoAuto = False Then
            objPRM(0) = ItemObject.ItemCode
        Else
            If Mode = FormAction.ADD Then
                objPRM(0) = GenerateItemNo()
            Else
                objPRM(0) = ItemObject.ItemCode
            End If
        End If
        objPRM(1) = ItemObject.Name
        objPRM(2) = ItemObject.Category
        objPRM(3) = ItemObject.Rate
        objPRM(4) = ItemObject.UOM
        objPRM(5) = ItemObject.Description
        objPRM(6) = ItemObject.IsInventoryItem
        objPRM(7) = ItemObject.SubCategory
        objPRM(8) = ItemObject.ROL

        If Mode = FormAction.ADD Then
            Dim objTemp As Object
            objTemp = DataCommon.db.ExecuteScalar("Proc_Insert_ItemServiceMaster", objPRM)
            If objTemp Is Nothing Then Return 0
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
            Return objTemp
        ElseIf Mode = FormAction.EDIT Then
            'If DataCommon.db.ExecuteNonQuery( "update_tbl_ItemMaster", objPRM) = 0 Then Exit Function
            'MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveMedicine(ByVal MedicineObject As HMSLibrary.MedicineItemsDtl, ByVal Mode As FormAction) As Boolean
        Dim objTemp As Object
        objTemp = SaveItem(MedicineObject, Mode)
        If objTemp Is Nothing Then Return False
        Dim objPRM(8) As Object
        objPRM(0) = objTemp
        objPRM(1) = MedicineObject.strenght
        objPRM(2) = MedicineObject.medicineType
        objPRM(3) = MedicineObject.genericDrug
        objPRM(4) = MedicineObject.mfg
        objPRM(5) = MedicineObject.composition
        objPRM(6) = MedicineObject.shelfNo
        objPRM(7) = MedicineObject.rackNo
        objPRM(8) = MedicineObject.boxNo
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_MedicineItemsDtl", objPRM) = 0 Then Return False
        ElseIf Mode = FormAction.EDIT Then
            'If DataCommon.db.ExecuteNonQuery( "update_tbl_ItemMaster", objPRM) = 0 Then Exit Function
            'MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Private Function GenerateItemNo() As Integer
        Try
            Dim strSql As String
            strSql = "SELECT ISNULL( MAX(ItemID),0) + 1 AS ID FROM ItemServiceMaster"
            strSql = DataCommon.db.ExecuteScalar(CommandType.Text, strSql)
            Return Val(strSql)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

    Public Overrides Function SaveLookup(ByVal LookupObject As HMSLibrary.Lookups, ByVal Mode As FormAction) As Boolean
        Dim objPRM(3) As Object
        objPRM(0) = LookupObject.Name
        objPRM(1) = LookupObject.LookupType
        objPRM(2) = LookupObject.Rate
        objPRM(3) = LookupObject.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_LookupsMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(4)
            objPRM(4) = LookupObject.ID
            If DataCommon.db.ExecuteNonQuery("Proc_Update_LookupsMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveVendor(ByVal VendorObject As HMSLibrary.Vendor, ByVal Mode As FormAction) As Boolean
        Dim objPRM(8) As Object
        objPRM(0) = VendorObject.Name.Trim
        objPRM(1) = VendorObject.Address.Trim
        objPRM(2) = VendorObject.Description
        objPRM(3) = VendorObject.TelNo
        objPRM(4) = VendorObject.MobileNo
        objPRM(5) = VendorObject.ContactPerson
        objPRM(6) = VendorObject.Email
        objPRM(7) = Val(VendorObject.Creditlimit)
        objPRM(8) = 0
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_SupplierMaster", objPRM) = False Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            'ReDim Preserve objPRM(13)
            'objPRM(13) = VendorObject.ID
            'If DataCommon.db.ExecuteNonQuery( "update_tbl_Supplier", objPRM) = False Then Return False
            'MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function ValidateUser(ByVal UserName As String, ByVal Password As String) As HMSLibrary.LoginInfo
        Dim userObject As New LoginInfo
        Try
            Dim strSql As String
            Dim dr As IDataReader
            strSql = "Select userid,password  from securitymaster where username = " & "'" & Trim(UserName) & "'"
            Dim objtemp As Object = Nothing
            dr = DataCommon.db.ExecuteReader(CommandType.Text, strSql)
            If dr.Read = True Then
                userObject.UserID = dr.Item("userid")
                userObject.Password = dr.Item("password")
            End If
            dr.Close()
            If userObject.UserID > 0 Then
                Dim securityObject As New HMS_Security
                If Trim(Password) = securityObject.setDecrypt(userObject.Password) Then
                    userObject.UserName = UserName
                    strSql = "SELECT [Company Name],ItemNoAuto FROM CompanyMaster"
                    dr = DataCommon.db.ExecuteReader(CommandType.Text, strSql)
                    If dr.Read = True Then
                        userObject.CompanyName = dr.Item(0)
                        If IsDBNull(dr.Item(1)) = False Then
                            userObject.isItemNoAuto = CType(dr.Item(1), Boolean)
                        End If
                    End If
                    dr.Close()
                    strSql = "SELECT ProfileID,IsSuperUser,IsLocked FROM SecurityMaster WHERE UserId = " & userObject.UserID
                    dr = DataCommon.db.ExecuteReader(CommandType.Text, strSql)
                    If dr.Read = True Then
                        userObject.ProfileID = IIf(IsDBNull(dr.Item("ProfileID")), 0, dr.Item("ProfileID"))
                        userObject.IsSuperUser = IIf(IsDBNull(dr.Item("IsSuperUser")), False, dr.Item("IsSuperUser"))
                        If IsDBNull(dr.Item("IsLocked")) = False Then
                            If CType(dr.Item("IsLocked"), Boolean) = False Then
                                MsgBox("Your account is locked by administrator.", MsgBoxStyle.OkOnly, CommonMessages.MessageTitle)
                                Return Nothing
                            End If
                        End If
                    End If
                    If userObject.IsSuperUser = False Then
                        Dim dataProvider As New MasterDataLayer()
                        userObject.ModulesList = dataProvider.GetModuleList(userObject.ProfileID)
                    End If
                    dr.Close()
                Else
                    MsgBox("Please check your user id or password", MsgBoxStyle.Critical, "Login failed!")
                    userObject.UserID = 0
                End If
            End If
        Catch ex As Exception
            Throw ex
        End Try
        Return userObject
    End Function

    Public Overloads Overrides Function GetLookupList() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetLookupList"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("ID")
                lup.Name = dr.Item("Name")
                lup.LookupType = dr.Item("LookupType")
                lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetUsersList() As System.Collections.Generic.List(Of HMSLibrary.Users)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Select [UserId] ,[UserName],[IsSuperUser] ,[IsLocked],ProfileID From [SecurityMaster] order by 2"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            Dim usersList As New List(Of Users)
            Dim usersObject As Users
            While dr.Read
                usersObject = New Users
                usersObject.Name = dr.Item("UserName")
                usersObject.ID = dr.Item("UserId")
                usersObject.IsLocked = IIf(IsDBNull(dr.Item("IsLocked")), 0, dr.Item("IsLocked"))
                usersObject.IsSuperUser = IIf(IsDBNull(dr.Item("IsSuperUser")), 0, dr.Item("IsSuperUser"))
                usersObject.ProfileID = IIf(IsDBNull(dr.Item("ProfileID")), 0, dr.Item("ProfileID"))
                usersList.Add(usersObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return usersList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetFinYearList() As System.Collections.Generic.List(Of HMSLibrary.FinancialYear)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Select [FinYearName],FinYearID From tbl_FinYear Order by 1 DESC"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            Dim finYearList As New List(Of FinancialYear)
            Dim financialYearObject As FinancialYear
            While dr.Read
                financialYearObject = New FinancialYear
                financialYearObject.ID = dr.Item("FinYearID")
                financialYearObject.Name = dr.Item("FinYearName")
                finYearList.Add(financialYearObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return finYearList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetBanksList() As System.Collections.Generic.List(Of HMSLibrary.Banks)
        Dim lst As New List(Of Banks)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From BankMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Banks
                lup.ID = dr.Item("BankID")
                lup.Name = dr.Item("BankName")
                lup.BranchName = dr.Item("BranchName")
                lup.Address = dr.Item("Address")
                lup.TelNo = dr.Item("TelNo")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetDepartmentsList() As System.Collections.Generic.List(Of HMSLibrary.Departments)
        Dim lst As New List(Of Departments)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From DepartmentMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Departments
                lup.ID = dr.Item("DeptID")
                lup.Name = dr.Item("DeptName")
                'lup.Charges = dr.Item("Charges")
                lup.Incharge = dr.Item("Incharge")
                lup.Description = dr.Item("Remarks")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetComplaintsList() As System.Collections.Generic.List(Of HMSLibrary.Complaints)
        Dim lst As New List(Of Complaints)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From DiseasesMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Complaints
                lup.ID = dr.Item("DID")
                lup.Name = dr.Item("Diseases")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetDoctorsList() As System.Collections.Generic.List(Of HMSLibrary.Doctors)
        Dim lst As New List(Of Doctors)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From DoctorMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Doctors
                lup.DrID = dr.Item("DrID")
                lup.FirstName = dr.Item("FirstName")
                lup.LastName = DataCommon.DBNullCheck(dr.Item("LastName"))
                lup.SurName = DataCommon.DBNullCheck(dr.Item("SurName"))
                lup.Address = DataCommon.DBNullCheck(dr.Item("Address"))
                lup.TelephoneNo = DataCommon.DBNullCheck(dr.Item("TelephoneNo"))
                lup.MobileNo = DataCommon.DBNullCheck(dr.Item("MobileNo"))
                lup.Gender = dr.Item("Gender")
                lup.Age = dr.Item("Age")
                lup.LicenseNo = dr.Item("LicenseNo")
                lup.ConsultationCharge = dr.Item("ConsultationCharge")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetEmployeesList() As System.Collections.Generic.List(Of HMSLibrary.Employees)
        Dim lst As New List(Of Employees)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From EmpMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Employees
                lup.EmpNo = dr.Item("EmpNo")
                lup.FirstName = dr.Item("FirstName")
                lup.LastName = DataCommon.DBNullCheck(dr.Item("LastName"))
                lup.SurName = DataCommon.DBNullCheck(dr.Item("SurName"))
                lup.Address = DataCommon.DBNullCheck(dr.Item("Address"))
                lup.TelephoneNo = DataCommon.DBNullCheck(dr.Item("Telephone No"))
                lup.MobileNo = DataCommon.DBNullCheck(dr.Item("Mobile No"))
                lup.Age = dr.Item("Age")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetRoomsList() As System.Collections.Generic.List(Of HMSLibrary.Rooms)
        Dim lst As New List(Of Rooms)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From RoomMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Rooms
                lup.ID = dr.Item("RoomID")
                lup.RoomNo = dr.Item("RoomNo")
                lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetWardsList() As System.Collections.Generic.List(Of HMSLibrary.Wards)
        Dim lst As New List(Of Wards)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select * From WardMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Wards
                lup.ID = dr.Item("WardID")
                lup.WardNo = dr.Item("WardNo")
                lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function SaveBanks(ByVal BankObject As HMSLibrary.Banks, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(3) As Object
        objPRM(0) = BankObject.Name
        objPRM(1) = BankObject.BranchName
        objPRM(2) = BankObject.Address
        objPRM(3) = BankObject.TelNo
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_Banks", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            objPRM(3) = BankObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveDepartments(ByVal DepartmentObject As HMSLibrary.Departments, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(3) As Object
        objPRM(0) = DepartmentObject.Name
        objPRM(1) = DepartmentObject.Charges
        objPRM(2) = DepartmentObject.Incharge
        objPRM(3) = DepartmentObject.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_DepartmentMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            objPRM(3) = objPRM(2) = DepartmentObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveComplaints(ByVal ComplaintObject As HMSLibrary.Complaints, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(0) As Object
        objPRM(0) = ComplaintObject.Name
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_DiseasesMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            objPRM(3) = ComplaintObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveDoctors(ByVal DoctorObject As HMSLibrary.Doctors, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(11) As Object
        objPRM(0) = DoctorObject.FirstName
        objPRM(1) = DoctorObject.LastName
        objPRM(2) = DoctorObject.SurName
        objPRM(3) = DoctorObject.Address
        objPRM(4) = DoctorObject.TelephoneNo
        objPRM(5) = DoctorObject.MobileNo
        objPRM(6) = DoctorObject.Gender
        objPRM(7) = DoctorObject.Age
        objPRM(8) = DoctorObject.LicenseNo
        objPRM(9) = DoctorObject.Specialization
        objPRM(10) = DoctorObject.DrType
        objPRM(11) = DoctorObject.ConsultationCharge
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_DoctorMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = DoctorObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveEmployees(ByVal EmployeeObject As HMSLibrary.Employees, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(9) As Object
        objPRM(0) = EmployeeObject.EmpNo
        objPRM(1) = EmployeeObject.FirstName
        objPRM(2) = EmployeeObject.LastName
        objPRM(3) = EmployeeObject.SurName
        objPRM(4) = EmployeeObject.Address
        objPRM(5) = EmployeeObject.TelephoneNo
        objPRM(6) = EmployeeObject.MobileNo
        objPRM(7) = EmployeeObject.EmpType
        objPRM(8) = EmployeeObject.Gender
        objPRM(9) = EmployeeObject.Age
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_EmpMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = LookupObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveRooms(ByVal RoomObject As HMSLibrary.Rooms, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(8) As Object
        objPRM(0) = RoomObject.RoomNo
        objPRM(1) = RoomObject.DeptID
        objPRM(2) = RoomObject.WardID
        objPRM(3) = RoomObject.RoomCost
        objPRM(4) = RoomObject.Description
        objPRM(5) = RoomObject.DrVisit
        objPRM(6) = RoomObject.NurseCharges
        objPRM(7) = RoomObject.IVCharges
        objPRM(8) = RoomObject.RoomType
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_RoomMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(9)
            objPRM(9) = RoomObject.ID
            If DataCommon.db.ExecuteNonQuery("Proc_Update_RoomMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveUsers(ByVal UserObject As HMSLibrary.Users, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(6) As Object
        objPRM(0) = UserObject.Name
        objPRM(1) = UserObject.Password
        objPRM(2) = 0
        objPRM(3) = UserObject.IsSuperUser
        objPRM(4) = ""
        objPRM(5) = ""
        objPRM(6) = UserObject.ProfileID
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_SecurityMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = UserObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveWards(ByVal WardObject As HMSLibrary.Wards, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(2) As Object
        objPRM(0) = WardObject.WardNo
        objPRM(1) = WardObject.DeptID
        objPRM(2) = WardObject.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_WardMaster", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            'ReDim Preserve objPRM(3)
            'objPRM(3) = WardObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function GetDoctorsLookList() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select DrID,FirstName,LastName,SurName From DoctorMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("DrID")
                lup.Name = DataCommon.DBNullCheck(dr.Item("SurName")) & " " & dr.Item("FirstName") & " " & DataCommon.DBNullCheck(dr.Item("LastName"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetEmployeesLookList() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select [EmpID],FirstName,LastName,SurName From EmpMaster Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("EmpID")
                lup.Name = DataCommon.DBNullCheck(dr.Item("SurName")) & " " & dr.Item("FirstName") & " " & DataCommon.DBNullCheck(dr.Item("LastName"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetModuleList(ByVal ProfileID As Integer) As System.Collections.Generic.List(Of HMSLibrary.ModulePermissions)
        Dim lst As New List(Of ModulePermissions)
        Try
            Dim dr As IDataReader
            SQLQuery = "EXEC Proc_GetModuleList " & ProfileID.ToString
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim mp As New ModulePermissions
                mp.ID = dr.Item("ModuleID")
                mp.Name = dr.Item("ModuleName")
                mp.ModuleType = dr.Item("ModuleType")
                mp.Add = IIf(IsDBNull(dr.Item("PAdd")), 0, dr.Item("PAdd"))
                mp.Edit = IIf(IsDBNull(dr.Item("PEdit")), 0, dr.Item("PEdit"))
                mp.Delete = IIf(IsDBNull(dr.Item("PDelete")), 0, dr.Item("PDelete"))
                mp.View = IIf(IsDBNull(dr.Item("PView")), 0, dr.Item("PView"))
                mp.Print = IIf(IsDBNull(dr.Item("PPrint")), 0, dr.Item("PPrint"))
                lst.Add(mp)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function SaveUserProfile(ByVal sender As HMSLibrary.UserProfile, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(0) As Object
        objPRM(0) = sender.Name
        If Mode = FormAction.ADD Then
            Dim objTemp As Object
            objTemp = DataCommon.db.ExecuteScalar("Proc_InsertUserProfile", objPRM)
            If objTemp Is Nothing Then Return False
            sender.ID = objTemp
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = BankObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        SaveProfilePermissionDetails(sender.ID, sender.ModulesList, Mode)
        Return True
    End Function

    Private Sub SaveProfilePermissionDetails(ByVal ProfileID As Integer, ByVal sender As List(Of ModulePermissions), ByVal Mode As HMSLibrary.FormAction)
        Dim objPRM(0) As Object
        objPRM(0) = ProfileID
        If Mode = FormAction.EDIT Then
            DataCommon.db.ExecuteNonQuery("Proc_DeleteModulePermissions", objPRM)
        End If
        ReDim Preserve objPRM(6)
        For Each mp In sender
            objPRM(1) = mp.ID
            objPRM(2) = mp.Add
            objPRM(3) = mp.Edit
            objPRM(4) = mp.Delete
            objPRM(5) = mp.View
            objPRM(6) = mp.Print
            DataCommon.db.ExecuteNonQuery("Proc_InsertModulePermissions", objPRM)
        Next
    End Sub

    Public Overrides Function GetUserProfile() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select [ProfileName],ProfileID From UserProfile Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("ProfileID")
                lup.Name = dr.Item("ProfileName")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetBillTemplate() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select [TemplateName],BillTemplateID From BillTemplates Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("BillTemplateID")
                lup.Name = dr.Item("TemplateName")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function SaveBillTemplate(ByVal sender As HMSLibrary.BillTemplate, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(2) As Object
        objPRM(0) = sender.Name
        objPRM(1) = sender.Description
        objPRM(2) = sender.CreatedBy
        If Mode = FormAction.ADD Then
            Dim objTemp As Object
            objTemp = DataCommon.db.ExecuteScalar("Proc_Insert_BillTemplates", objPRM)
            If objTemp Is Nothing Then Return False
            sender.ID = objTemp
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = BankObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        SaveBillTemplateDetails(sender.ID, sender.ItemsList, Mode)
        Return True
    End Function

    Private Sub SaveBillTemplateDetails(ByVal BillTemplateID As Integer, ByVal sender As List(Of BillTemplateItems), ByVal Mode As HMSLibrary.FormAction)
        Dim objPRM(0) As Object
        objPRM(0) = BillTemplateID
        If Mode = FormAction.EDIT Then
            DataCommon.db.ExecuteNonQuery("Proc_Delete_BillTemplateDetails", objPRM)
        End If
        ReDim Preserve objPRM(2)
        For Each mp In sender
            objPRM(1) = mp.ItemID
            objPRM(2) = mp.ItemOrder
            DataCommon.db.ExecuteNonQuery("Proc_Insert_BillTemplateDetails", objPRM)
        Next
    End Sub

    Public Overrides Function GetTemplateItemsList(ByVal TemplateID As Integer) As System.Collections.Generic.List(Of HMSLibrary.ItemsService)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "EXEC Proc_GetTemplateItems " & TemplateID.ToString
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            Dim itemsList As New List(Of ItemsService)
            Dim itemsObject As ItemsService
            While dr.Read
                itemsObject = New ItemsService
                itemsObject.Name = dr.Item("Name")
                itemsObject.ItemCode = dr.Item("ItemNo")
                itemsObject.ID = dr.Item("ItemID")
                itemsObject.Rate = DataCommon.DBNullCheck(dr.Item("Rate"))
                itemsObject.UOMName = DataCommon.DBNullCheck(dr.Item("UnitName"))
                itemsObject.UOM = DataCommon.DBNullCheck(dr.Item("Unit"))
                itemsList.Add(itemsObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return itemsList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetAlertsList() As System.Collections.Generic.List(Of HMSLibrary.Alerts)
        Dim lst As New List(Of Alerts)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select [AlertSubject],[AlertNote],[AlertDate],AlertStatus From [AlertsReminder] Order by [AlertDate]"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Alerts
                lup.Subject = dr.Item("AlertSubject")
                lup.Note = IIf(IsDBNull(dr.Item("AlertNote")), String.Empty, dr.Item("AlertNote"))
                lup.AlertDate = dr.Item("AlertDate")
                lup.AlertStatus = dr.Item("AlertStatus")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function SaveAlert(ByVal sender As HMSLibrary.Alerts, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(3) As Object
        objPRM(0) = sender.Subject
        objPRM(1) = sender.Note
        objPRM(2) = sender.AlertDate
        objPRM(3) = sender.CreatedBy
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_AlertsReminder", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = BankObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function GetLookupTypeList() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select [LookupType],LookupTypeID From LookupTypes Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("LookupTypeID")
                lup.Name = dr.Item("LookupType")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetFindingsCategoryList(ByVal category As Integer) As System.Collections.Generic.List(Of HMSLibrary.Finding)
        Dim lst As New List(Of Finding)
        Try
            Dim dr As IDataReader
            SQLQuery = "Exec Proc_GetFindingCategoryList " & category
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Finding
                lup.ID = dr.Item("FindingID")
                lup.Name = dr.Item("Finding")
                lup.findingCategoryName = dr.Item("FindingCategory")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetFindingsList() As System.Collections.Generic.List(Of HMSLibrary.Finding)
        Dim lst As New List(Of Finding)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetFindingsList"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            While dr.Read
                Dim lup As New Finding
                lup.ID = dr.Item("FindingID")
                lup.Name = dr.Item("Finding")
                lup.findingCategoryName = dr.Item("FindingCategory")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetInvestigationList() As System.Collections.Generic.List(Of HMSLibrary.Investigation)
        Dim lst As New List(Of Investigation)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetInvestigationsList"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            While dr.Read
                Dim lup As New Investigation
                lup.ID = dr.Item("InvestigationID")
                lup.Name = dr.Item("Investigation")
                lup.investigationCategoryName = dr.Item("InvestigationCategory")
                lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetProblemsList() As System.Collections.Generic.List(Of HMSLibrary.Problems)
        Dim lst As New List(Of Problems)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetProblemsList"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            While dr.Read
                Dim lup As New Problems
                lup.ID = dr.Item("ProblemID")
                lup.Name = dr.Item("Problem")
                lup.problemCategoryName = dr.Item("ProblemCategory")
                lup.ChiefComplaint = IIf(IsDBNull(dr.Item("ChiefComplaint")), False, dr.Item("ChiefComplaint"))
                lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetTreatmentProceduresList() As System.Collections.Generic.List(Of HMSLibrary.TreatmentProcedure)
        Dim lst As New List(Of TreatmentProcedure)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetTreatmentProceduresList"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New TreatmentProcedure
                lup.ID = dr.Item("ProcedureID")
                lup.Name = dr.Item("ProcedureDesc")
                lup.procedureCategoryName = dr.Item("ProcedureCategory")
                lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function SaveFinding(ByVal sender As HMSLibrary.Finding, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(1) As Object
        objPRM(0) = sender.findingCategory
        objPRM(1) = sender.Name
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_Findings", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(2)
            objPRM(2) = sender.ID
            If DataCommon.db.ExecuteNonQuery("Proc_UpdateFinding", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveInvestigation(ByVal sender As HMSLibrary.Investigation, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(2) As Object
        objPRM(0) = sender.Name
        objPRM(1) = sender.investigationCategory
        objPRM(2) = sender.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_Investigation", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            objPRM(3) = sender.ID
            If DataCommon.db.ExecuteNonQuery("Proc_UpdateInvestigations", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveProblem(ByVal sender As HMSLibrary.Problems, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(3) As Object
        objPRM(0) = sender.Name
        objPRM(1) = sender.problemCategory
        objPRM(2) = sender.ChiefComplaint
        objPRM(3) = sender.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_Problem", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(4)
            objPRM(4) = sender.ID
            If DataCommon.db.ExecuteNonQuery("Proc_UpdateProblem", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function SaveTreatmentProcedures(ByVal sender As HMSLibrary.TreatmentProcedure, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(2) As Object
        objPRM(0) = sender.Name
        objPRM(1) = sender.procedureCategory
        objPRM(2) = sender.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_TreatmentProcedures", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            objPRM(3) = sender.ID
            If DataCommon.db.ExecuteNonQuery("Proc_UpdateTreatmentProcedures", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function GetProblemsCategoryWiseList(ByVal categoryID As Integer) As System.Collections.Generic.List(Of HMSLibrary.Problems)
        Dim lst As New List(Of Problems)
        Try
            Dim dr As IDataReader
            SQLQuery = "EXEC Proc_GetProblemsCategoryWiseList " & categoryID.ToString
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Problems
                lup.ID = dr.Item("ProblemID")
                lup.Name = dr.Item("Problem")
                lup.problemCategoryName = dr.Item("ProblemCategory")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetDiagnosisList() As ObservableCollection(Of HMSLibrary.Diagnosis)
        Dim lst As New ObservableCollection(Of Diagnosis)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetDiagnosisList"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            Dim lup As Diagnosis
            While dr.Read
                If IsDBNull(dr.Item("ParentID")) Then
                    lup = New Diagnosis
                    lup.ID = dr.Item("DiagnosisID")
                    lup.Name = dr.Item("Diagnosis")
                    lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                    lst.Add(lup)
                Else
                    If Val(dr.Item("ParentID")) > 0 Then
                        Dim parent As Diagnosis = Nothing
                        lup = New Diagnosis
                        parent = GetDiagnosisParent(lst, dr.Item("ParentID"))
                        lup.ID = dr.Item("DiagnosisID")
                        lup.Name = dr.Item("Diagnosis")
                        lup.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                        lup.parentName = parent.Name
                        parent.subItems.Add(lup)
                    End If
                End If
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Private Function GetDiagnosisParent(ByVal sender As ObservableCollection(Of Diagnosis), ByVal ParentID As Integer) As Diagnosis
        Dim diagnosisObject
        If diagnosisObject Is Nothing Then
            diagnosisObject = (From dia In sender Where dia.ID = ParentID Select dia).FirstOrDefault
            If diagnosisObject IsNot Nothing Then Return diagnosisObject
            If sender.Count > 0 Then
                For Each dia In sender
                    diagnosisObject = GetDiagnosisParent(dia.subItems, ParentID)
                    If diagnosisObject IsNot Nothing Then Return diagnosisObject
                Next
            End If
        End If
        Return diagnosisObject
    End Function

    Public Overrides Function SaveDiagnosis(ByVal sender As HMSLibrary.Diagnosis, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(2) As Object
        objPRM(0) = sender.Name
        objPRM(1) = sender.parentID
        objPRM(2) = sender.Description
        If Mode = FormAction.ADD Then
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_Diagnosis", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            objPRM(3) = sender.ID
            If DataCommon.db.ExecuteNonQuery("Proc_Update_Diagnosis", objPRM) = 0 Then Return False
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        Return True
    End Function

    Public Overrides Function GetComplaintLookList() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select ProblemID,Problem From Problems WHERE ChiefComplaint = 1 Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("ProblemID")
                lup.Name = dr.Item("Problem")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetBoxNos() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select Distinct BoxNo From MedicineItemsDtl Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.Name = dr.Item("BoxNo")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetRackNos() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select Distinct RackNo From MedicineItemsDtl Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.Name = dr.Item("RackNo")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetShelfNos() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select Distinct ShelfNo From MedicineItemsDtl Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.Name = dr.Item("ShelfNo")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetMedicineItemList() As System.Collections.Generic.List(Of HMSLibrary.MedicineItemsDtl)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Proc_GetMedicineItemsDtl"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            Dim itemsList As New List(Of MedicineItemsDtl)
            Dim itemsObject As MedicineItemsDtl
            While dr.Read
                itemsObject = New MedicineItemsDtl
                itemsObject.Name = dr.Item("Name")
                itemsObject.ItemCode = dr.Item("ItemNo")
                itemsObject.ID = dr.Item("ItemID")
                itemsObject.CategoryName = DataCommon.DBNullCheck(dr.Item("Category"))
                itemsObject.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                itemsObject.SubCategoryName = DataCommon.DBNullCheck(dr.Item("SubCategory"))
                itemsObject.genericDrugName = DataCommon.DBNullCheck(dr.Item("GenericDrug"))
                itemsObject.genericDrug = DataCommon.DBNullCheck(dr.Item("GenericDrugID"))
                itemsObject.mfg = DataCommon.DBNullCheck(dr.Item("MfgID"))
                itemsObject.mfgName = DataCommon.DBNullCheck(dr.Item("Mfg"))
                itemsObject.medicineType = DataCommon.DBNullCheck(dr.Item("MedicineTypeID"))
                itemsObject.medicineTypeName = DataCommon.DBNullCheck(dr.Item("MedicineType"))
                itemsObject.strenght = DataCommon.DBNullCheck(dr.Item("Strenght"))
                itemsObject.composition = DataCommon.DBNullCheck(dr.Item("Composition"))
                itemsObject.shelfNo = DataCommon.DBNullCheck(dr.Item("ShelfNo"))
                itemsObject.rackNo = DataCommon.DBNullCheck(dr.Item("RackNo"))
                itemsObject.boxNo = DataCommon.DBNullCheck(dr.Item("BoxNo"))
                itemsList.Add(itemsObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return itemsList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetPrescriptionTemplateItemsList(ByVal TemplateID As Integer) As System.Collections.Generic.List(Of HMSLibrary.PatientPrescription)
        Dim lst As New List(Of PatientPrescription)
        Try
            Dim dr As IDataReader
            SQLQuery = "Exec Proc_Select_PrescriptionTempDetails " & TemplateID
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New PatientPrescription
                lup.medicineItemID = dr.Item("MedicineItemID")
                lup.medicineItem = dr.Item("Name")
                lup.dosage = DataCommon.DBNullCheck(dr.Item("Dosage"))
                lup.frequencyName = DataCommon.DBNullCheck(dr.Item("FrequencyName"))
                lup.frequency = DataCommon.DBNullCheck(dr.Item("Frequency"))
                lup.duaration = DataCommon.DBNullCheck(dr.Item("Duaration"))
                lup.periods = DataCommon.DBNullCheck(dr.Item("Periods"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function SavePrescriptionTemplate(ByVal sender As HMSLibrary.PrescriptionTemplate, ByVal Mode As HMSLibrary.FormAction) As Boolean
        Dim objPRM(1) As Object
        objPRM(0) = sender.prescription
        objPRM(1) = sender.remarks
        If Mode = FormAction.ADD Then
            Dim objTemp As Object
            objTemp = DataCommon.db.ExecuteScalar("Proc_Insert_PrescriptionTemplate", objPRM)
            If objTemp Is Nothing Then Return False
            sender.prescriptionTempID = objTemp
            MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        ElseIf Mode = FormAction.EDIT Then
            ReDim Preserve objPRM(3)
            'objPRM(3) = BankObject.ID
            'If DataCommon.db.ExecuteNonQuery( "Proc_Update_LookupsMaster", objPRM) = 0 Then Exit Function
            MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
        End If
        SavePrescriptionTemplateDetails(sender.prescriptionTempID, sender.ItemsList, Mode)
        Return True
    End Function

    Private Sub SavePrescriptionTemplateDetails(ByVal PrescriptionTemplateID As Integer, ByVal sender As ObservableCollection(Of PatientPrescription), ByVal Mode As HMSLibrary.FormAction)
        Dim objPRM(0) As Object
        objPRM(0) = PrescriptionTemplateID
        If Mode = FormAction.EDIT Then
            DataCommon.db.ExecuteNonQuery("Proc_Delete_PrescriptionTempDetails", objPRM)
        End If
        ReDim Preserve objPRM(6)
        Dim i As Short = 0
        For Each mp In sender
            i = i + 1
            objPRM(1) = i
            objPRM(2) = mp.medicineItemID
            objPRM(3) = mp.dosage
            objPRM(4) = mp.frequency
            objPRM(5) = mp.duaration
            objPRM(6) = mp.periods
            DataCommon.db.ExecuteNonQuery("Proc_Insert_PrescriptionTempDetails", objPRM)
        Next
    End Sub

    Public Overrides Function GetMedicineLookupItems() As System.Collections.Generic.List(Of HMSLibrary.MedicineItemsDtl)
        Dim lst As New List(Of MedicineItemsDtl)
        Try
            Dim dr As IDataReader
            SQLQuery = "Proc_GetMedicineLookupItems"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            While dr.Read
                Dim lup As New MedicineItemsDtl
                lup.ID = dr.Item("ItemID")
                lup.Name = dr.Item("Name")
                lup.genericDrug = DataCommon.DBNullCheck(dr.Item("GenericDrug"))
                lup.mfg = DataCommon.DBNullCheck(dr.Item("Mfg"))
                lup.medicineType = DataCommon.DBNullCheck(dr.Item("MedicineType"))
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetPrescriptionTemplate() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Select PrescriptionTempID,Prescription From PrescriptionTemplate Order by 1"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.Name = dr.Item("Prescription")
                lup.ID = dr.Item("PrescriptionTempID")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetItemServiceList() As System.Collections.Generic.List(Of HMSLibrary.ItemsService)
        Try
            Dim dr As IDataReader = Nothing
            SQLQuery = "Proc_GetItemMaster"
            dr = DataCommon.db.ExecuteReader(CommandType.StoredProcedure, SQLQuery)
            Dim itemsList As New List(Of ItemsService)
            Dim itemsObject As ItemsService
            While dr.Read
                itemsObject = New ItemsService
                itemsObject.Name = dr.Item("Name")
                itemsObject.ItemCode = dr.Item("ItemNo")
                itemsObject.ID = dr.Item("ItemID")
                itemsObject.Rate = DataCommon.DBNullCheck(dr.Item("Rate"))
                itemsObject.CategoryName = DataCommon.DBNullCheck(dr.Item("Category"))
                itemsObject.UOMName = DataCommon.DBNullCheck(dr.Item("UnitName"))
                itemsObject.UOM = DataCommon.DBNullCheck(dr.Item("Unit"))
                itemsObject.Description = DataCommon.DBNullCheck(dr.Item("Remarks"))
                itemsObject.SubCategoryName = DataCommon.DBNullCheck(dr.Item("SubCategory"))
                itemsObject.IsInventoryItem = DataCommon.DBNullCheck(dr.Item("IsInventoryItem"))
                itemsList.Add(itemsObject)
            End While
            If dr.IsClosed = False Then dr.Close()
            Return itemsList
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Function GetRoomsLookupList() As System.Collections.Generic.List(Of HMSLibrary.Lookups)
        Dim lst As New List(Of Lookups)
        Try
            Dim dr As IDataReader
            SQLQuery = "Exec Proc_GetRoomLookups"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim lup As New Lookups
                lup.ID = dr.Item("RoomID")
                lup.Name = dr.Item("RoomNo")
                lst.Add(lup)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function
End Class
